﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Globalization;
using System.Linq;
using System.Web;

public class BLLLaundry
{
    DatabaseDataContext Context;

    public BLLLaundry()
	{
        Context = new DatabaseDataContext();
	}
    #region Fabrics
    public List<Fabric> GetFabrics(string sortExp, string sortDirection, int pageIndex, String filterSearch, int startRow,
        int pageSize, string sortColumns)
    {
        List<Fabric> list = new List<Fabric>();
        try
        {
            sortColumns = sortExp + " " + sortDirection;
            startRow = pageIndex * pageSize;
            var fabrics = (from p in Context.Fabrics select p);
            if (!String.IsNullOrEmpty(filterSearch))
            {
                fabrics = fabrics.Where(l => l.Name.Contains(filterSearch));
            }
            
            if (!String.IsNullOrEmpty(sortColumns))
            {
                if (sortColumns.Contains("DESC"))
                {
                    if (sortColumns.Contains("Name"))
                        fabrics = fabrics.OrderByDescending(l => l.Name);
                }
                else
                {
                    if (sortColumns.Contains("Name"))
                        fabrics = fabrics.OrderBy(l => l.Name);
                }
            }
            list = fabrics.Skip(startRow).Take(pageSize).ToList();
        }
        catch
        {}
        if (list.Count == 0)
        {
            Fabric p = new Fabric();
            p.Id = -1;
            list.Add(p);
        }
        return list;
    }
    public int GetFabricsCount(string sortExp, string sortDirection, int pageIndex, String filterSearch)
    {
        int count = 0;

        try
        {
            var fabrics = (from p in Context.Fabrics select p);
            if (!String.IsNullOrEmpty(filterSearch))
            {
                fabrics = fabrics.Where(l => l.Name.Contains(filterSearch));
            }

            count = fabrics.Count();
        }
        catch
        { }
        if (count == 0)
        {
            count = 1;
        }
        return count;
    }
    public bool UpdateFabric(Fabric obj)
    {
        bool result = false;
        try
        {
            Fabric fabric = (from f in Context.Fabrics where f.Id == obj.Id select f).First();
            if (fabric != null)
            {
                fabric.Name = obj.Name;
                fabric.Description = obj.Description;
                Context.SubmitChanges();
                result = true;
            }
        }
        catch
        {
        }
        return result;
    }
    public bool InsertFabric(Fabric obj)
    {
        bool result = false;
        try
        {
            Context.Fabrics.InsertOnSubmit(obj);
            Context.SubmitChanges();
            result = true;
        }
        catch
        {
        }
        return result;
    }
    public bool DeleteFabric(int Id)
    {
        bool result = false;
        try
        {
            Fabric fabric = (from f in Context.Fabrics where f.Id == Id select f).First();
            if (fabric != null)
            {
                Context.Fabrics.DeleteOnSubmit(fabric);
                Context.SubmitChanges();
                result = true;
            }
        }
        catch
        {
        }
        return result;
    }
    public List<Fabric> GetFabrics()
    {
        List<Fabric> list = new List<Fabric>();
        try
        {
            list = (from f in Context.Fabrics select f).OrderBy(o => o.Name).ToList();
        }
        catch
        { }
        return list;
    }
    #endregion
    #region Sizes
    public List<Size> GetSizes(string sortExp, string sortDirection, int pageIndex, String filterSearch, int startRow,
        int pageSize, string sortColumns)
    {
        List<Size> list = new List<Size>();
        try
        {
            sortColumns = sortExp + " " + sortDirection;
            startRow = pageIndex * pageSize;
            var size = (from s in Context.Sizes select s);
            if (!String.IsNullOrEmpty(filterSearch))
            {
                size = size.Where(l => l.Name.Contains(filterSearch));
            }

            if (!String.IsNullOrEmpty(sortColumns))
            {
                if (sortColumns.Contains("DESC"))
                {
                    if (sortColumns.Contains("Name"))
                        size = size.OrderByDescending(l => l.Name);
                }
                else
                {
                    if (sortColumns.Contains("Name"))
                        size = size.OrderBy(l => l.Name);
                }
            }
            list = size.Skip(startRow).Take(pageSize).ToList();
        }
        catch
        { }
        if (list.Count == 0)
        {
            Size p = new Size();
            p.Id = -1;
            list.Add(p);
        }
        return list;
    }
    public int GetSizesCount(string sortExp, string sortDirection, int pageIndex, String filterSearch)
    {
        int count = 0;

        try
        {
            var size = (from s in Context.Sizes select s);
            if (!String.IsNullOrEmpty(filterSearch))
            {
                size = size.Where(l => l.Name.Contains(filterSearch));
            }

            count = size.Count();
        }
        catch
        { }
        if (count == 0)
        {
            count = 1;
        }
        return count;
    }
    public bool UpdateSize(Size obj)
    {
        bool result = false;
        try
        {
            Size size = (from s in Context.Sizes where s.Id == obj.Id select s).First();
            if (size != null)
            {
                size.Name = obj.Name;
                Context.SubmitChanges();
                result = true;
            }
        }
        catch
        {
        }
        return result;
    }
    public bool InsertSize(Size obj)
    {
        bool result = false;
        try
        {
            Context.Sizes.InsertOnSubmit(obj);
            Context.SubmitChanges();
            result = true;
        }
        catch
        {
        }
        return result;
    }
    public bool DeleteSize(int Id)
    {
        bool result = false;
        try
        {
            Size size = (from s in Context.Sizes where s.Id == Id select s).First();
            if (size != null)
            {
                Context.Sizes.DeleteOnSubmit(size);
                Context.SubmitChanges();
                result = true;
            }
        }
        catch
        {
        }
        return result;
    }
    #endregion
    #region Models
    public List<Model> GetModels(string sortExp, string sortDirection, int pageIndex, String filterSearch, int startRow,
        int pageSize, string sortColumns)
    {
        List<Model> list = new List<Model>();
        try
        {
            sortColumns = sortExp + " " + sortDirection;
            startRow = pageIndex * pageSize;
            var Models = (from p in Context.Models select p);
            if (!String.IsNullOrEmpty(filterSearch))
            {
                Models = Models.Where(l => l.Name.Contains(filterSearch));
            }
            if (!String.IsNullOrEmpty(sortColumns))
            {
                if (sortColumns.Contains("DESC"))
                {
                    if (sortColumns.Contains("Name"))
                        Models = Models.OrderByDescending(l => l.Name);
                }
                else
                {
                    if (sortColumns.Contains("Name"))
                        Models = Models.OrderBy(l => l.Name);
                }
            }
            list = Models.Skip(startRow).Take(pageSize).ToList();
        }
        catch
        { }
        if (list.Count == 0)
        {
            Model p = new Model();
            p.Id = -1;
            list.Add(p);
        }
        return list;
    }
    public int GetModelsCount(string sortExp, string sortDirection, int pageIndex, String filterSearch)
    {
        int count = 0;

        try
        {
            var Models = (from p in Context.Models select p);
            if (!String.IsNullOrEmpty(filterSearch))
            {
                Models = Models.Where(l => l.Name.Contains(filterSearch));
            }

            count = Models.Count();
        }
        catch
        { }
        if (count == 0)
        {
            count = 1;
        }
        return count;
    }
    public bool InsertModel(Model m)
    {
        bool result = false;
        try
        {
            Context.Models.InsertOnSubmit(m);
            Context.SubmitChanges();
            result = true;
        }
        catch
        {
        }
        return result;
    }
    public bool UpdateModel(Model m)
    {
        bool result = false;
        try
        {
            Model model = (from p in Context.Models where p.Id == m.Id select p).First();
            if (model != null)
            {
                model.Name = m.Name;
                model.Description = m.Description;
                Context.SubmitChanges();
                result = true;
            }
        }
        catch
        {
        }
        return result;
    }
    public bool DeleteModel(long Id)
    {
        bool result = false;
        try
        {
            Model model = (from p in Context.Models where p.Id == Id select p).First();
            if (model != null)
            {
                Context.Models.DeleteOnSubmit(model);
                Context.SubmitChanges();
                result = true;
            }
        }
        catch
        {
        }
        return result;
    }
    public List<Model> GetModels()
    {
        List<Model> list = new List<Model>();
        try
        {
            list = (from m in Context.Models select m).OrderBy(o => o.Name).ToList();
        }
        catch
        { }
        return list;
    }
    #endregion
    #region Production
    public class Full_Production
    {
        public long Id { get; set; }
        public long ModelId { get; set; }
        public String Gender { get; set; }
        public String ModelName { get; set; }
        public String FabricsName { get; set; }
        public DateTime Date { get; set; }
        public long FabricId { get; set; }
        public long? ProcessId { get; set; }
        public int TotalPants { get; set; }
        public double Width { get; set; }
        public double NumberByProduction { get; set; }
        public List<PantsByProduction> PantsByProductions { get; set; }
        public List<sizesByProduction> sizes { get; set; }
    }
    public class sizesByProduction
    {
        public String Name { get; set; }
        public long SizeId { get; set; }
        public int Quantity { get; set; }
    }
    public bool Save(Production pr, List<sizesByProduction> sizes)
    {
        bool result = false;
        try
        {
            Production production = pr;
            if (pr.Id > 0)
            {
                production = (from p in Context.Productions where p.Id == pr.Id select p).First();
                production.ModelId = pr.ModelId;
                production.Gender = pr.Gender;
                production.Date = pr.Date;
                production.FabricId = pr.FabricId;
                production.ProcessId = pr.ProcessId;
                production.Width = pr.Width;
                production.NumberByProduction = pr.NumberByProduction;
                var s = (from si in Context.PantsByProductions where si.ProductionId == pr.Id select si);
                Context.PantsByProductions.DeleteAllOnSubmit(s);
            }
            else
            {
                Context.Productions.InsertOnSubmit(pr);
            }
            Context.SubmitChanges();
            List<PantsByProduction> pants = new List<PantsByProduction>();
            foreach (sizesByProduction p in sizes)
            {
                PantsByProduction pb = new PantsByProduction();
                pb.ProductionId = pr.Id;
                pb.Quantity = p.Quantity;
                pb.SizeId = p.SizeId;
                pb.CreatedDate = DateTime.Now;
                pb.CreatedBy = Util.GetCurrentUserId();
                pants.Add(pb);
            }
            Context.PantsByProductions.InsertAllOnSubmit(pants);
            Context.SubmitChanges();
            result = true;
        }
        catch
        {
        }
        return result;
    }
    public Production getProductionById(long Id)
    {
        Production pr = null;
        try
        {
            pr = (from p in Context.Productions where p.Id == Id select p).First();
        }
        catch
        {
        }
        return pr;
    }
    public Full_Production getFullProductionById(long Id)
    {
        Full_Production fp = new Full_Production();
        try
        {
            var pr = (from p in Context.Productions where p.Id == Id select p).First();
            fp.Id = pr.Id;
            fp.ModelId = pr.ModelId;
            fp.Date = pr.Date;
            fp.FabricId = pr.FabricId;
            fp.Gender = pr.Gender;
            fp.NumberByProduction = pr.NumberByProduction;
            fp.Width = pr.Width;
            fp.ProcessId = pr.ProcessId;
            List<sizesByProduction> sizes = new List<sizesByProduction>();
            List<PantsByProduction> pants = pr.PantsByProductions.ToList();
            var allS = (from s in Context.Sizes select s).ToList();
            foreach (Size sp in allS)
            {
                sizesByProduction sbp = new sizesByProduction();
                sbp.Name = sp.Name;
                sbp.SizeId = sp.Id;
                try
                {
                    var qy = (from q in pants where q.SizeId == sp.Id select q).First();
                    sbp.Quantity = qy.Quantity;
                }
                catch { }
                sizes.Add(sbp);
            }
            fp.sizes = sizes;
        }
        catch
        {
        }
        return fp;
    }
    public List<sizesByProduction> getSizes()
    {
        List<sizesByProduction> fp = new List<sizesByProduction>();
        try
        {
            fp = (from p in Context.Sizes
                  select new sizesByProduction
                  { 
                Name = p.Name,
                Quantity = 0,
                SizeId = p.Id
            }).ToList();
        }
        catch
        {
        }
        return fp;
    }

    public List<Full_Production> GetProductions(string sortExp, string sortDirection, int pageIndex, String filterSearch, int startRow,
       int pageSize, string sortColumns)
    {
        Full_Production fp = new Full_Production();
        List<Full_Production> list = new List<Full_Production>();
        int index = 0;
        try
        {
            sortColumns = sortExp + " " + sortDirection;
            startRow = pageIndex * pageSize;
            var productions = (from prod in Context.Productions
                         select new Full_Production()
                         {
                            Id = prod.Id,
                            ModelId = prod.ModelId,
                            ModelName = prod.Model.Name,
                            Date = prod.Date,
                            FabricId = prod.FabricId,
                            Gender = prod.Gender,
                            NumberByProduction = prod.NumberByProduction,
                            Width = prod.Width,
                            FabricsName = prod.Fabric.Name,
                            PantsByProductions = prod.PantsByProductions.ToList()
                            
                         });

            
            if (!String.IsNullOrEmpty(filterSearch))
            {
                filterSearch = filterSearch.ToLower();
                productions = productions.Where(p => p.ModelName.ToLower().Contains(filterSearch) 
                    ||  p.FabricsName.ToLower().Contains(filterSearch));
            }

            if (!String.IsNullOrEmpty(sortColumns))
            {
                if (sortColumns.Contains("DESC"))
                {
                    if (sortColumns.Contains("Date"))
                        productions = productions.OrderByDescending(l => l.Date);
                    if (sortColumns.Contains("ModelName"))
                        productions = productions.OrderByDescending(l => l.ModelName);
                    if (sortColumns.Contains("FabricsName"))
                        productions = productions.OrderByDescending(l => l.FabricsName);

                }
                else
                {
                    if (sortColumns.Contains("Date"))
                        productions = productions.OrderBy(l => l.Date);
                    if (sortColumns.Contains("ModelName"))
                        productions = productions.OrderBy(l => l.ModelName);
                    if (sortColumns.Contains("FabricsName"))
                        productions = productions.OrderBy(l => l.FabricsName);
                }
            }
            list = productions.Skip(startRow).Take(pageSize).ToList();
        }
        catch
        { }
        if (list.Count == 0)
        {
            Full_Production us = new Full_Production();
            us.Id = -1;
            list.Add(us);
        }
        /*Getting Total Pants*/
        foreach(Full_Production pr in list)
        {
            List<sizesByProduction> sizes = new List<sizesByProduction>();
            List<PantsByProduction> pants = pr.PantsByProductions;
            var allS = (from s in Context.Sizes select s).ToList();
            foreach (Size sp in allS)
            {
                sizesByProduction sbp = new sizesByProduction();
                sbp.Name = sp.Name;
                sbp.SizeId = sp.Id;
                try
                {
                    var qy = (from q in pants where q.SizeId == sp.Id select q).First();
                    sbp.Quantity = qy.Quantity;
                }
                catch { }
                sizes.Add(sbp);
            }
            list[index].TotalPants = (from s in sizes select s.Quantity).Sum();
            index++;
        }
        return list;
    }
    public int GetProductionsCount(string sortExp, string sortDirection, int pageIndex, String filterSearch)
    {
        int count = 0;

        try
        {
            var productions = (from prod in Context.Productions
                               select new Full_Production()
                               {
                                   Id = prod.Id,
                                   ModelId = prod.ModelId,
                                   ModelName = prod.Model.Name,
                                   Date = prod.Date,
                                   FabricId = prod.FabricId,
                                   Gender = prod.Gender,
                                   NumberByProduction = prod.NumberByProduction,
                                   Width = prod.Width,
                                   FabricsName = prod.Fabric.Name,
                                   PantsByProductions = prod.PantsByProductions.ToList()
                               });
            if (!String.IsNullOrEmpty(filterSearch))
            {
                filterSearch = filterSearch.ToLower();
                productions = productions.Where(p => p.ModelName.ToLower().Contains(filterSearch)
                    || p.FabricsName.ToLower().Contains(filterSearch));
            }

            count = productions.Count();
        }
        catch
        { }
        if (count == 0)
        {
            count = 1;
        }
        return count;
    }
    public bool DeleteProductions(int Id)
    {
        bool result = false;
        try
        {
            Production prod = (from p in Context.Productions where p.Id == Id select p).First();
            if (prod != null)
            {
                var s = (from si in Context.PantsByProductions where si.ProductionId == prod.Id select si);
                Context.PantsByProductions.DeleteAllOnSubmit(s);
                Context.Productions.DeleteOnSubmit(prod);
                Context.SubmitChanges();
                result = true;
            }
        }
        catch
        {
        }
        return result;
    }
    public int GetPantsByProductionId(int Id)
    {
        int count = 0;
        try
        {

            var production = (from prod in Context.Productions where prod.Id == Id
                               select new Full_Production()
                               {
                                   Id = prod.Id,
                                   ModelId = prod.ModelId,
                                   ModelName = prod.Model.Name,
                                   Date = prod.Date,
                                   FabricId = prod.FabricId,
                                   Gender = prod.Gender,
                                   NumberByProduction = prod.NumberByProduction,
                                   Width = prod.Width,
                                   FabricsName = prod.Fabric.Name,
                                   PantsByProductions = prod.PantsByProductions.ToList()

                               }).ToList().First();

            List<sizesByProduction> sizes = new List<sizesByProduction>();
            List<PantsByProduction> pants = production.PantsByProductions;
            var allS = (from s in Context.Sizes select s).ToList();
            foreach (Size sp in allS)
            {
                sizesByProduction sbp = new sizesByProduction();
                sbp.Name = sp.Name;
                sbp.SizeId = sp.Id;
                try
                {
                    var qy = (from q in pants where q.SizeId == sp.Id select q).First();
                    sbp.Quantity = qy.Quantity;
                }
                catch { }
                sizes.Add(sbp);
            }
            count = (from s in sizes select s.Quantity).Sum();
           
            
            
        }
        catch
        {
        }
        return count;
    }
    
    #endregion
    #region Lost Pants
    public class Full_LostPants
    {
        public long Id { get; set; }
        public long SizeId { get; set; }
        public long ProductionId { get; set; }
        public String SizeName { get; set; }
        public int Quantity { get; set; }
        public DateTime Date { get; set; }
    }
    public List<Full_LostPants> GetLostPants(long productionId, string sortExp, string sortDirection, int pageIndex, String filterSearch, int startRow,
       int pageSize, string sortColumns)
    {
        Full_LostPants lp = new Full_LostPants();
        List<Full_LostPants> list = new List<Full_LostPants>();
        int index = 0;
        try
        {
            sortColumns = sortExp + " " + sortDirection;
            startRow = pageIndex * pageSize;
            var lostPants = (from p in Context.LostPants where p.ProductionId == productionId
                             select new Full_LostPants()
                         {
                            Id = p.Id,
                            SizeId = p.SizeId,
                            ProductionId = p.ProductionId,
                            Date = p.Date,
                            SizeName = p.Size.Name,
                            Quantity = p.Quantity
                         });

            
           if (!String.IsNullOrEmpty(filterSearch))
            {
                filterSearch = filterSearch.ToLower();
                lostPants = lostPants.Where(p => p.SizeName.ToLower().Contains(filterSearch) );
            }

            if (!String.IsNullOrEmpty(sortColumns))
            {
                if (sortColumns.Contains("DESC"))
                {
                    if (sortColumns.Contains("Date"))
                        lostPants = lostPants.OrderByDescending(l => l.Date);
                    if (sortColumns.Contains("SizeName"))
                        lostPants = lostPants.OrderByDescending(l => l.SizeName);
                    if (sortColumns.Contains("Quantity"))
                        lostPants = lostPants.OrderByDescending(l => l.Quantity);

                }
                else
                {
                    if (sortColumns.Contains("Date"))
                        lostPants = lostPants.OrderBy(l => l.Date);
                    if (sortColumns.Contains("SizeName"))
                        lostPants = lostPants.OrderBy(l => l.SizeName);
                    if (sortColumns.Contains("Quantity"))
                        lostPants = lostPants.OrderBy(l => l.Quantity);
                }
            }
            list = lostPants.Skip(startRow).Take(pageSize).ToList();
        }
        catch
        { }
        if (list.Count == 0)
        {
            Full_LostPants us = new Full_LostPants();
            us.Id = -1;
            list.Add(us);
        }
        
        return list;
    }
    public int GetLostPantsCount(long productionId, string sortExp, string sortDirection, int pageIndex, String filterSearch)
    {
        int count = 0;

        try
        {
            var lostPants = (from p in Context.LostPants
                             where p.ProductionId == productionId
                             select new Full_LostPants()
                             {
                                 Id = p.Id,
                                 SizeId = p.SizeId,
                                 ProductionId = p.ProductionId,
                                 Date = p.Date,
                                 SizeName = p.Size.Name
                             });
            if (!String.IsNullOrEmpty(filterSearch))
            {
                filterSearch = filterSearch.ToLower();
                lostPants = lostPants.Where(p => p.SizeName.ToLower().Contains(filterSearch));
            }

            count = lostPants.Count();
        }
        catch
        { }
        if (count == 0)
        {
            count = 1;
        }
        return count;
    }
    public Full_LostPants getFullLostPantById(long Id)
    {
        Full_LostPants fp = new Full_LostPants();
        try
        {
            var p = (from lp in Context.LostPants where lp.Id == Id select lp).First();
            fp.Id = p.Id;
            fp.SizeId = p.SizeId;
            fp.Date = p.Date;
            fp.Quantity = p.Quantity;
        }
        catch
        {
        }
        return fp;
    }

    public List<Size> GetSizesByProductionId(long ProductionId)
    {
        List<Size> s = new List<Size>();
        try
        {
            s = (from size in Context.Sizes where (from p in Context.PantsByProductions where p.ProductionId ==
                                                            ProductionId select p.SizeId).Contains(size.Id) select size).ToList();
        }
        catch
        {
        }
        return s;
    }
    public bool SaveLostPants(LostPant lp)
    {
        bool result = false;
        try
        {
            LostPant lostp = lp;
            if (lp.Id > 0)
            {
                lostp = (from p in Context.LostPants where p.Id == lp.Id select p).First();
                lostp.SizeId = lp.SizeId;
                lostp.Date = lp.Date;
                lostp.Quantity = lp.Quantity;
                lostp.ProductionId = lp.ProductionId;
                
            }
            else
            {
                Context.LostPants.InsertOnSubmit(lp);
            }
            Context.SubmitChanges();
            
            result = true;
        }
        catch
        {
        }
        return result;
    }
    public bool DeleteLostPants(int Id)
    {
        bool result = false;
        try
        {
            LostPant lp = (from l in Context.LostPants where l.Id == Id select l).First();
            if (lp != null)
            {
                Context.LostPants.DeleteOnSubmit(lp);
                Context.SubmitChanges();
                result = true;
            }
        }
        catch
        {
        }
        return result;
    }
    #endregion
    #region Received Note
    public class Full_Detail
    {
        public long Id { get; set; }
        public long ProcessId { get; set; }
        public String Quantity { get; set; }
        public String Price { get; set; }
        public String Tax { get; set; }
        public List<Process> Processes { get; set; }
    }
    public class ReceivedNotedInfo
    {
        public long Id { get; set; }
        public long ClientId { get; set; }
        public String ClientName { get; set; }
        public String ClientDocumentId { get; set; }
        public DateTime Date { get; set; }
        public double total { get; set; }
        public double tax { get; set; }
    }
    /// <summary>
    /// Get the list of Received Notes store in database.
    /// </summary>
    /// <param name="sortExp">The column used to order the list</param>
    /// <param name="sortDirection">Indicates the direction to order the list ASC / DESC</param>
    /// <param name="pageIndex">Indicates the page that should be returned</param>
    /// <param name="filterSearch">The phrase to filter results, this phrase must be used to filter results by client name and client document id</param>
    /// <param name="startRow">Not Used</param>
    /// <param name="pageSize">Number of rows showed on list</param>
    /// <param name="sortColumns">Not Used</param>
    public List<ReceivedNotedInfo> GetReceivedNotes(string sortExp, string sortDirection, int pageIndex, String filterSearch, int startRow,
       int pageSize, string sortColumns)
    {
        List<ReceivedNotedInfo> list = new List<ReceivedNotedInfo>();
        try
        {
            sortColumns = sortExp + " " + sortDirection;
            startRow = pageIndex * pageSize;
            var notes = (from prod in Context.ReceivedNotes
                         select new ReceivedNotedInfo()
                               {
                                   Id = prod.Id,
                                   ClientId = prod.Client.Id,
                                   ClientName = prod.Client.Name,
                                   ClientDocumentId = prod.Client.DocumentId,
                                   Date = prod.Date,
                               });


            if (!String.IsNullOrEmpty(filterSearch))
            {
                filterSearch = filterSearch.ToLower();
                notes = notes.Where(p => p.ClientName.ToLower().Contains(filterSearch)
                    || p.ClientDocumentId.ToLower().Contains(filterSearch));
            }

            if (!String.IsNullOrEmpty(sortColumns))
            {
                if (sortColumns.Contains("DESC"))
                {
                    if (sortColumns.Contains("Date"))
                        notes = notes.OrderByDescending(l => l.Date);
                    if (sortColumns.Contains("ClientName"))
                        notes = notes.OrderByDescending(l => l.ClientName);
                    if (sortColumns.Contains("ClientDocumentId"))
                        notes = notes.OrderByDescending(l => l.ClientDocumentId);

                }
                else
                {
                    if (sortColumns.Contains("Date"))
                        notes = notes.OrderBy(l => l.Date);
                    if (sortColumns.Contains("ClientName"))
                        notes = notes.OrderBy(l => l.ClientName);
                    if (sortColumns.Contains("ClientDocumentId"))
                        notes = notes.OrderBy(l => l.ClientDocumentId);
                }
            }
            list = notes.Skip(startRow).Take(pageSize).ToList();
        }
        catch
        { }
        if (list.Count == 0)
        {
            ReceivedNotedInfo us = new ReceivedNotedInfo();
            us.Id = -1;
            list.Add(us);
        }
        
        return list;
    }
    /// <summary>
    /// Get the Total rows storored in database
    /// </summary>
    /// <param name="sortExp">Not Used</param>
    /// <param name="sortDirection">Not Used</param>
    /// <param name="pageIndex">Indicates the page that should be returned</param>
    /// <param name="filterSearch">The phrase to filter results, this phrase must be used to filter results by client name and client document id</param>
    public int GetReceivedNotesCount(string sortExp, string sortDirection, int pageIndex, String filterSearch)
    {
        int count = 0;

        try
        {
            var notes = (from prod in Context.ReceivedNotes
                         select new ReceivedNotedInfo()
                         {
                             Id = prod.Id,
                             ClientId = prod.Client.Id,
                             ClientName = prod.Client.Name,
                             ClientDocumentId = prod.Client.DocumentId,
                             Date = prod.Date,
                         });
            if (!String.IsNullOrEmpty(filterSearch))
            {
                filterSearch = filterSearch.ToLower();
                notes = notes.Where(p => p.ClientName.ToLower().Contains(filterSearch)
                    || p.ClientDocumentId.ToLower().Contains(filterSearch));
            }

            count = notes.Count();
        }
        catch
        { }
        if (count == 0)
        {
            count = 1;
        }
        return count;
    }

    public ReceivedNote GetReceivedNoteById(long Id)
    {
        ReceivedNote rn = null;
        try
        {
            rn = (from r in Context.ReceivedNotes where r.Id == Id select r).First();
        }
        catch
        {
        }
        return rn;
    }
    public List<Full_Detail> GetReceivedNoteDetail(long NoteId)
    {
        List<Full_Detail> List = new List<Full_Detail>();
        try
        {
            BLLProcess DB = new BLLProcess();
            List<Process> processes = DB.GetProcesses();
            List<Detail> details = (from r in Context.Details where r.ReceivedNoteId == NoteId select r).ToList();
            foreach (Detail d in details)
            {
                Full_Detail fd = new Full_Detail { 
                Id = d.Id,
                Price = Math.Round(d.Price, 2).ToString().Replace(ConfigurationManager.AppSettings["ThousandSeparator"], ConfigurationManager.AppSettings["DecimalSeparator"]),
                ProcessId = d.ProcessId,
                Quantity = d.Quantity.ToString(),
                Processes = processes,
                Tax = d.ReceivedNote.Tax.ToString().Replace(ConfigurationManager.AppSettings["ThousandSeparator"], ConfigurationManager.AppSettings["DecimalSeparator"])
                };
                List.Add(fd);
            }
            
        }
        catch
        {
        }
        return List;
    }
    public bool SaveReceivedNote(ReceivedNote r, List<Full_Detail> details, bool decrease, out long id)
    {
        bool result = false;
        id = 0;
        try
        {
            Client c = SaveClient(r.Client);
            if(c != null)
            {
                ReceivedNote rn = null;
                int max = 0;
                bool insert = false;
                if (r.Id > 0)
                {
                    rn = (from rnote in Context.ReceivedNotes where rnote.Id == r.Id select rnote).First();
                    rn.Note = r.Note;
                    rn.Date = r.Date;
                    var d = (from det in Context.Details where det.ReceivedNoteId == r.Id select det);
                    Context.Details.DeleteAllOnSubmit(d);
                }
                else
                {
                    try
                    {
                        max = (from m in Context.ReceivedNotes where m.ClientId == c.Id select m.Code).Max() + 1;
                    }
                    catch { }
                    BLLSettings bllSet = new BLLSettings();
                    Setting s = bllSet.GetSettings();
                    rn = new ReceivedNote();
                    rn.ClientId = c.Id;
                    rn.Date = r.Date;
                    rn.Note = r.Note;
                    rn.Code = max;
                    rn.Tax = s.Tax;
                    rn.CreatedBy = r.CreatedBy;
                    rn.CreatedDate = r.CreatedDate;
                    Context.ReceivedNotes.InsertOnSubmit(rn);
                    insert = true;
                }
                Context.SubmitChanges();
                id = rn.Id;
                if (insert)
                {
                    rn.txtCode = c.Id.ToString() + "-" + max.ToString().PadLeft(10, '0');
                    Context.SubmitChanges();
                }
                List<Detail> dt = new List<Detail>();
                NumberFormatInfo nfi = new CultureInfo( "en-US", false ).NumberFormat;
                nfi.NumberDecimalSeparator = ConfigurationManager.AppSettings["DecimalSeparator"];
                Dictionary<long, int> processes = new Dictionary<long, int>();
                foreach (Full_Detail fd in details)
                {
                    if (fd.Id >= 0)
                    {
                        Detail d = new Detail();
                        double price = 0;
                        price = Convert.ToDouble(fd.Price,nfi);
                        d.Price = price;
                        d.ProcessId = fd.ProcessId;
                        int qyt = Convert.ToInt32(fd.Quantity);
                        d.Quantity = qyt;
                        d.ReceivedNoteId = rn.Id;
                        dt.Add(d);
                        if (processes.ContainsKey(fd.ProcessId))
                        {
                            processes[fd.ProcessId] += qyt;
                        }
                        else {
                            processes.Add(fd.ProcessId, qyt);
                        }
                    }
                }
                Context.Details.InsertAllOnSubmit(dt);
                Context.SubmitChanges();
                if (decrease)
                {
                    BLLSettings bllSet = new BLLSettings();
                    Setting settings = bllSet.GetSettings();
                    Dictionary<long, decimal> products = new Dictionary<long, decimal>();
                    Dictionary<long, decimal> totalProducts = new Dictionary<long, decimal>();

                    foreach (KeyValuePair<long, int> pair in processes)
                    {
                        Dictionary<long, decimal> totalByProcess = (from total in (from s in Context.Stages
                                join sd in Context.StageDetails on s.Id equals sd.StageId where s.ProcessId == pair.Key
                                select new 
                                {
                                    productId = sd.ProductId, 
                                    total = sd.Quantity
                                }) group total by new { productId = total.productId } into tmp
                                select new { productId = tmp.Key.productId, total = tmp.Sum(o => o.total) }).ToDictionary(d => d.productId,d => d.total);
                        decimal pesoPant = (decimal)((pair.Value * settings.PantWeight) / 1000); //calculando el peso total (en Kg) de los pantalones para el proceso seleccionado
                        foreach (KeyValuePair<long, decimal> pairT in totalByProcess)
                        {
                            decimal totalKgDecrease = (pesoPant * pairT.Value); //cantidad de producto a decrementar del inventario en gr/ml
                            if (totalProducts.ContainsKey(pairT.Key))
                            {
                                totalProducts[pairT.Key] += totalKgDecrease;
                            }
                            else
                            {
                                totalProducts.Add(pairT.Key, totalKgDecrease);
                            }
                        }
                    }
                    try{
                        foreach (KeyValuePair<long, decimal> pairT in totalProducts)
                        {
                            var pr = (from p in Context.Products where p.Id == pairT.Key select p).First();
                            decimal total = pairT.Value;
                            if (pr.Unit == GlobalEnums.Units.kg.ToString() || pr.Unit == GlobalEnums.Units.l.ToString())
                            {
                                total = total / 1000;
                            }
                            if (pr.Quantity == null)
                                pr.Quantity = (double)((-1) * total);
                            else
                                pr.Quantity = (double)(Convert.ToDecimal(pr.Quantity) - total);
                            Context.SubmitChanges();
                        }
                    }catch{}
                }
                
                result = true;
            }
        }
        catch
        {
        }
        return result;
    }
    public Client SaveClient(Client c)
    {
        Client client = null;
        try
        {
            var check = (from cl in Context.Clients where cl.DocumentId == c.DocumentId select cl).ToList();
            if (check.Count > 0)
            {
                client = check[0];
                check[0].Name = c.Name;
                check[0].Address = c.Address;
                check[0].Phone = c.Phone;
            }
            else {
                client = new Client();
                client.Name = c.Name;
                client.DocumentId = c.DocumentId;
                client.Address = c.Address;
                client.Phone = c.Phone;
                client.CreatedDate = DateTime.Now;
                client.CreatedBy = c.CreatedBy;
                Context.Clients.InsertOnSubmit(client);
            }
            Context.SubmitChanges();
        }
        catch { 
            
        }
        return client;
    }
    public List<CustomClient> GetClientsList(string name)
    {
        List<CustomClient> clients = new List<CustomClient>();
        try
        {
            clients = (from c in Context.Clients where c.Name.ToLower().StartsWith(name.ToLower())
                       select new CustomClient
                        {
                            Id = c.Id,
                            Name = c.Name,
                            Document = c.DocumentId,
                            Address = c.Address,
                            Phone = c.Phone
                            
                        } ).ToList();
            
            
        }
        catch
        {

        }
        return clients;
    }
    public List<CustomClient> GetClientsListByDocument(string document)
    {
        List<CustomClient> clients = new List<CustomClient>();
        try
        {
            clients = (from c in Context.Clients
                       where c.DocumentId.ToLower().StartsWith(document.ToLower())
                       select new CustomClient
                       {
                           Id = c.Id,
                           Name = c.Name,
                           Document = c.DocumentId,
                           Address = c.Address,
                           Phone = c.Phone

                       }).ToList();


        }
        catch
        {

        }
        return clients;
    }
    public bool DeleteReceivedNote(int Id)
    {
        bool result = false;
        try
        {
            var details = (from p in Context.Details where p.ReceivedNoteId == Id select p);
            Context.Details.DeleteAllOnSubmit(details);
            Context.SubmitChanges();
            var notes = (from n in Context.ReceivedNotes where n.Id == Id select n);
            Context.ReceivedNotes.DeleteAllOnSubmit(notes);
            Context.SubmitChanges();
        }
        catch
        {
        }
        return result;
    }
    #endregion
    /// <summary>
    /// Get the list of Sales store in database.
    /// </summary>
    /// <param name="sortExp">The column used to order the list</param>
    /// <param name="sortDirection">Indicates the direction to order the list ASC / DESC</param>
    /// <param name="pageIndex">Indicates the page that should be returned</param>
    /// <param name="filterSearch">The phrase to filter results, this phrase must be used to filter results by client name and client document id</param>
    /// <param name="startRow">Not Used</param>
    /// <param name="pageSize">Number of rows showed on list</param>
    /// <param name="sortColumns">Not Used</param>
    public List<ReceivedNotedInfo> GetSales(string sortExp, string sortDirection, int pageIndex, String filterSearch, int startRow,
       int pageSize, string sortColumns)
    {
        List<ReceivedNotedInfo> resp = new List<ReceivedNotedInfo>();
        try
        {
            sortColumns = sortExp + " " + sortDirection;
            startRow = pageIndex * pageSize;
            var notes = (from prod in Context.ReceivedNotes
                         select new ReceivedNotedInfo()
                         {
                             Id = prod.Id,
                             ClientId = prod.Client.Id,
                             ClientName = prod.Client.Name,
                             ClientDocumentId = prod.Client.DocumentId,
                             Date = prod.Date,
                             tax = prod.Tax,
                             total = (from d in Context.Details where d.ReceivedNoteId == prod.Id select d.Price * d.Quantity).Sum()
                         });


            if (!String.IsNullOrEmpty(filterSearch) && filterSearch != "0")
            {
                int cID = 0;
                int.TryParse(filterSearch, out cID);
                notes = notes.Where(p => p.ClientId == cID);
            }

            if (!String.IsNullOrEmpty(sortColumns))
            {
                if (sortColumns.Contains("DESC"))
                {
                    if (sortColumns.Contains("Date"))
                        notes = notes.OrderByDescending(l => l.Date);
                    if (sortColumns.Contains("ClientName"))
                        notes = notes.OrderByDescending(l => l.ClientName);
                    if (sortColumns.Contains("ClientDocumentId"))
                        notes = notes.OrderByDescending(l => l.ClientDocumentId);

                }
                else
                {
                    if (sortColumns.Contains("Date"))
                        notes = notes.OrderBy(l => l.Date);
                    if (sortColumns.Contains("ClientName"))
                        notes = notes.OrderBy(l => l.ClientName);
                    if (sortColumns.Contains("ClientDocumentId"))
                        notes = notes.OrderBy(l => l.ClientDocumentId);
                }
            }
            
            var list = notes.Skip(startRow).Take(pageSize).ToList();
            resp = (from prod in list
                        select new ReceivedNotedInfo()
                        {
                            Id = prod.Id,
                            ClientId = prod.ClientId,
                            ClientName = prod.ClientName,
                            ClientDocumentId = prod.ClientDocumentId,
                            Date = prod.Date,
                            tax = prod.tax,
                            total = Math.Round((((prod.total * prod.tax) / 100) + prod.total),2)
                        }).ToList();
        }
        catch
        { }
        if (resp.Count == 0)
        {
            ReceivedNotedInfo us = new ReceivedNotedInfo();
            us.Id = -1;
            resp.Add(us);
        }

        return resp;
    }
    /// <summary>
    /// Get the Total rows storored in database
    /// </summary>
    /// <param name="sortExp">Not Used</param>
    /// <param name="sortDirection">Not Used</param>
    /// <param name="pageIndex">Indicates the page that should be returned</param>
    /// <param name="filterSearch">The phrase to filter results, this phrase must be used to filter results by client name and client document id</param>
    public int GetSalesCount(string sortExp, string sortDirection, int pageIndex, String filterSearch)
    {
        int count = 0;

        try
        {
            var notes = (from prod in Context.ReceivedNotes
                         select new ReceivedNotedInfo()
                         {
                             Id = prod.Id,
                             ClientId = prod.Client.Id,
                             ClientName = prod.Client.Name,
                             ClientDocumentId = prod.Client.DocumentId,
                             Date = prod.Date,
                         });
            if (!String.IsNullOrEmpty(filterSearch) && filterSearch != "0")
            {
                int cID = 0;
                int.TryParse(filterSearch, out cID);
                notes = notes.Where(p => p.ClientId == cID);
            }

            count = notes.Count();
        }
        catch
        { }
        if (count == 0)
        {
            count = 1;
        }
        return count;
    }
}